<?php

//
session_start();
//
if (!(isset($_SESSION['login']) && $_SESSION['login'] != '')) {

    header("Location: login.php");
}
include 'i_functions.php';
include 'i_csv2SQL.php';

//
function GetClientData($db_handle, $Data2) {
    $pcode = $Data2[15];
    $sname = $Data2[6];
    if ($pcode) {
        $q_pcode = quote_smart($pcode, $db_handle);
        $SQL3 = "SELECT * FROM client WHERE Postcode = $q_pcode";
        $result3 = mysql_query($SQL3);
        $num_rows3 = mysql_num_rows($result3);
        if ($result3) {
            if ($num_rows3 > 0) {
                $message = " - client already exists:";
                while ($ClientRow = mysql_fetch_assoc($result3)) {
                    $ID3 = $ClientRow['ID'];
                    $_SESSION['Work']['ClientID'] = $ID3;
                    //print "<br>" . $num_rows3 . " Client ID=" . $_SESSION['Work']['ClientID'];
                }
            } else {
                //create client
                $MapID3 = "NNR_Cases_to_client";
                $MapRec3 = array();
                $Data3 = array();
                $c3 = "";
                $UE_Opts3['Exit'] = "A";
                $UE_Func3 = $MapID3;
                $UE_Code3 = $MapID3 . ".php";
                if (is_file($UE_Code3)) {
                    include $UE_Code3;
                    $Val = $UE_Func3($MapRec3, $Data3, $c3, $UE_Opts3);
                }
                //----
                $SQL3_init = csv2SQL_init($MapID3);
                $MapRec3 = $SQL3_init[0];
                $csvFile3 = $MapRec3['csvFile'];
                $TableName3 = $MapRec3['TableName'];
                $ColType = explode(",", $MapRec3['DataType']);
                print "<br>";
                //var_dump($ColType);
                if (strpos($csvFile3, ".csv")) {
                    $dlim = ",";
                } else {
                    $dlim = "\t";
                }
                //print "<br>" . $SQL3_init[1];
                $SQL3_Vals = csv2SQL_vals($MapID3, $MapRec3, $dlim, $Data2);
                $SQL3 = $SQL3_init[1] . $SQL3_Vals;
                print "<br>SQL3: " . $SQL3_init[1] . $SQL3_Vals;
                $result3 = mysql_query($SQL3);
                $ID3 = mysql_insert_id();
                $_SESSION['Work']['ClientID'] = $ID3;
                print "<br>*** client " . $ID3 . " created";
            }
        }
    }
}

//
function GetLenderData($db_handle, $Data2) {
    $Lender = $Data2[19];
    if ($Lender) {
        $L1 = explode(" ", $Lender);
        $L1 = $L1[0];
        //print "<br>Checking for lender " . $L1;
        $q_Lender = quote_smart($L1 . "%", $db_handle);
        $SQL4 = "SELECT * FROM lenders WHERE LenderName LIKE $q_Lender";
        //print " - " . $SQL4;
        $result4 = mysql_query($SQL4);
        $num_rows4 = mysql_num_rows($result4);
        //print " - " . $num_rows4;
        if ($result4) {
            if ($num_rows4 == 1) {
                while ($LenderRow = mysql_fetch_assoc($result4)) {
                    $_SESSION['Work']['LenderID'] = $LenderRow['ID'];
                    print "<br>lender=" . $_SESSION['Work']['LenderID'];
                }
            }
        }
    }
}

//
function AddComments($db_handle, $Data2) {
    $ClientID = $_SESSION['Work']['ClientID'];
    $ClaimID = $_SESSION['Work']['ClaimID'];
    $LastAction = $Data2[31];
    $Comment = $Data2[33];
    $Status = $Data2[38];
    if (!$Status) {
        $Status = "null";
    } else {
        $Status = "'" . $Status . "'";
    }
    //print "<br>" . $LastAction . " | " . $Comment . " | " . $Status;
    $SQL5 = "INSERT INTO `activity` (`ClaimID`, `ClientID`, `Type`, `Status`, `Details`) ";
    if ($LastAction) {
        $SQL5_Vals = "VALUES ('$ClaimID', '$ClientID', 'S', $Status, '$LastAction')";
        $result5 = mysql_query($SQL5 . $SQL5_Vals);
        print "<br>" . $SQL5 . $SQL5_Vals . " - " . $result5;
    }
    if ($Comment) {
        $SQL5_Vals = "VALUES ('$ClaimID', '$ClientID', 'M', $Status, '$Comment')";
        $result5 = mysql_query($SQL5 . $SQL5_Vals);
        print "<br>" . $SQL5 . $SQL5_Vals . " - " . $result5;
    }
}

//
// M A I N   L O G I C
//
$MapID2 = "NNR_Cases_to_claims";
$MapRec2 = array();
$Data2 = array();
$c = "";
$_SESSION['Work'] = array();
$UE_Opts['Exit'] = "A";
$UE_Func = $MapID2;
$UE_Code = $MapID2 . ".php";
if (is_file($UE_Code)) {
    include $UE_Code;
    $Val = $UE_Func($MapRec2, $Data2, $c, $UE_Opts);
} else {
//
}
//
//==========================================
//	CONNECT TO THE LOCAL DATABASE
//==========================================
$user_name = "root";
$pass_word = "haddons";
$database = "PPI";
$server = "127.0.0.1";
$csvFile = "";
//
include 'i_head.php';
//
$db_handle = mysql_connect($server, $user_name, $pass_word);
$db_found = mysql_select_db($database, $db_handle);
//
if ($db_found) {
// insert claims into database
    $SQL2_init = csv2SQL_init($MapID2);
    $MapRec2 = $SQL2_init[0];
    $csvFile2 = $MapRec2['csvFile'];
    $TableName2 = $MapRec2['TableName'];
    if (strpos($csvFile2, ".csv")) {
        $dlim = ",";
    } else {
        $dlim = "\t";
    }
    print "<br>" . $SQL2_init[1];
    if (file_exists($csvFile2)) {
        $file_handle = fopen($csvFile2, "r");
        $lnct = 1;
        while (!feof($file_handle)) {
            $Data2 = explode($dlim, fgets($file_handle));
            if ($lnct > 1) {
                $_SESSION['Work']['ClientID'] = "";
                $_SESSION['Work']['LenderID'] = "";
                GetClientData($db_handle, $Data2);
                GetLenderData($db_handle, $Data2);
                $SQL2_Vals = csv2SQL_vals($MapID2, $MapRec2, $dlim, $Data2);
                $SQL2 = $SQL2_init[1] . $SQL2_Vals;
                print "<br>" . $SQL2_Vals;
                $result2 = mysql_query($SQL2, $db_handle);
                $ID2 = mysql_insert_id($db_handle);
                $_SESSION['Work']['ClaimID'] = $ID2;
                print " *** claim " . $ID2 . " created";
                AddComments($db_handle, $Data2, $_SESSION['Work']);
                $errorMessage = "Error logging on";
            } else {
                //skip header
            }
            $lnct++;
        }
    }
} else {
    print "<br>couldn't find $csvFile";
}
mysql_close($db_handle);
?>